<?php
$results = false;

$newdbname = 'sis_courseInfo';
$tblpref = 'courselist_';
$today = date("Y-m-d");
$yesterday = date("Y-m-d",strtotime($today." -1 day"));
$today = date("Y_m_d",strtotime($today));
$yesterday = date("Y_m_d",strtotime($yesterday));
$todaytbl = $tblpref.$today;
$yesterdaytbl = $tblpref.$yesterday;
$sql = "SHOW TABLES FROM $newdbname LIKE '{$tblpref}%'";
global $tbls;
if($sql = mysql_query($sql,$GLOBALS['lflink'])) {
	$tbls = mysql_num_rows($sql);
}
else {
	$tbls = 0;
}

if($tbls == 0) {
	$mslink = mssql_connect(base64_decode(MSSERVE),base64_decode(MSUSR),base64_decode(MSPSS));
	$tname = 'Results';
}
else {
	while($t = mysql_fetch_array($sql)) {
		$tname = '`'.$newdbname.'`.`'. $t[0] .'`';
	}
}

$locations = array();
$locset = false;

/* Set up the custom sorting of our results, based on the locations - Middletown should display first, then Fauquier Campus, then everything else in alpha order, then Web-based */
$la = array_keys($course_locations);
$la = array_flip($la);
$order_locations = "
CASE LOCATION ";
foreach($la as $k=>$v) {
	$order_locations .= "
	WHEN '".$k."' THEN ".($v+1);
}
$order_locations .= "
	ELSE ".(count($course_locations)+1)."
END";

/* Set up the reverse of our custom sorting by location for our improvised "LIMIT" clause */
$la = array_reverse(array_keys($course_locations),FALSE);
$la = array_flip($la);

$order_locations_backwards = "
CASE LOCATION";
foreach($la as $k=>$v) {
	$order_locations_backwards .= "
	WHEN '".$k."' THEN ".($v+1);
}
$order_locations_backwards .= "
	ELSE ".(count($course_locations)+1)."
END";

$s = "SELECT LOCATION FROM $tname";
if(isset($_GET['id']) && is_numeric($_GET['id']) && strlen($_GET['id']) == 4) {
	$s .= "
WHERE STRM=".$_GET['id']; 
}
elseif(isset($_GET['STRM'])) {
	if(is_array($_GET['STRM'])) {
		foreach($_GET['STRM'] as $k=>$t) {
			if(!is_numeric($t)) {
				unset($_GET['STRM'][$k]);
			}
		}
		$s .= "
WHERE STRM IN (".implode(',',$_GET['STRM']).")";
	}
	elseif(is_numeric($_GET['STRM'])) {
		$s .= "
WHERE STRM=".$_GET['STRM'];
	}
}
else {
	$s .= "
WHERE STRM>=".CURTERM." AND STRM <=".MAXTERM;
}
$s .= " 
GROUP BY LOCATION 
ORDER BY $order_locations";

echo "\n<!--\nOur Locations SQL Query\n";
var_dump( $s );
echo "\n-->\n";

$mslink = (isset($mslink)) ? $mslink : NULL;
if($s = sql_query($s,$mslink)) {
	while($r = sql_fetch_assoc($s)) {
		$locations[] = $r['LOCATION'];
	}
	sql_free_result($s);
}
else {
/*	print 'There was an error retrieving the locations.';
	die(mssql_get_last_message());*/
	$locations[] = 'NOWHERE';
}

$course_subjects = array();
$s = "SELECT ACADEMICSUBJECT, SUBJECT FROM $tname WHERE STRM>=".CURTERM." AND STRM <=".MAXTERM." GROUP BY ACADEMICSUBJECT, SUBJECT";
if($s = sql_query($s,$mslink)) {
	while($r = sql_fetch_assoc($s)) {
		$course_subjects[$r['SUBJECT']] = $r['ACADEMICSUBJECT'];
	}
	sql_free_result($s);
}
asort($course_subjects);

$terms = array();
$s = "SELECT TERM, STRM FROM $tname WHERE STRM>=".CURTERM." AND STRM <=".MAXTERM." GROUP BY TERM, STRM";
if($s = sql_query($s,$mslink)) {
	while($r = sql_fetch_assoc($s)) {
#		print "\n<!-- ".$r['STRM'].' = '.$r['TERM']." -->\n";
		$terms[$r['STRM']] = $r['TERM'];
	}
	sql_free_result($s);
}
ksort($terms);
?>